package com.g2room.myuall.util;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class DbUtil {
	static Connection connection = null;
	public static Connection getConn() {
		if(null == connection) {
			try {
	//				connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
	//						"jdbc:mysql://203.171.236.56:3306/myuall?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true", 
	//						"root", "qwe!@#");
	
	//			connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
	//					"jdbc:mysql://127.0.0.1:3306/myuall?useUnicode=true&characterEncoding=utf8&mysqlEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull", 
	//					"op", "!wlms*yts_asw");
					connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
							"jdbc:mysql://127.0.0.1:3306/myuall_2017?useUnicode=true&characterEncoding=utf8&mysqlEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull", 
							"root", "123456");
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return connection;
	}
	public static Connection getOlConn() {
		if(null == connection) {
			try {
	//				connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
	//						"jdbc:mysql://203.171.236.56:3306/myuall?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true", 
	//						"root", "qwe!@#");
	
	//			connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
	//					"jdbc:mysql://127.0.0.1:3306/myuall?useUnicode=true&characterEncoding=utf8&mysqlEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull", 
	//					"op", "!wlms*yts_asw");
					connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
							"jdbc:mysql://127.0.0.1:3306/myuall_ol?useUnicode=true&characterEncoding=utf8&mysqlEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull", 
							"root", "");
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return connection;
	}
	public static Connection getSaConn() {
		if(null == connection) {
			try {
				connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
						"jdbc:mysql://127.0.0.1:3306/myuall_ol?useUnicode=true&characterEncoding=utf8&mysqlEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull", 
						"root", "");
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return connection;
	}
	public static Connection getComConn() {
//		if(null == connection) {
			try {
				connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
						"jdbc:mysql://127.0.0.1:3306/life_grap?useUnicode=true&characterEncoding=utf8&mysqlEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull", 
						"root", "123456");
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
//		}
		return connection;
	}
	public static Connection getConn(String db) {
		if(null == connection) {
		try {
			connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
					"jdbc:mysql://127.0.0.1:3306/"+db+"?useUnicode=true&characterEncoding=utf8&mysqlEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull", 
					"root", "");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		}
		return connection;
	}
	public static Connection getNewsConn() {
		if(null == connection) {
			try {
				connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
						"jdbc:mysql://127.0.0.1:3306/news?useUnicode=true&characterEncoding=utf8&mysqlEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull", 
						"root", "");
//						"jdbc:mysql://rm-bp1y3tih0vawe18v1.mysql.rds.aliyuncs.com:3306/r4g9sj29e2?useUnicode=true&characterEncoding=utf8&mysqlEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull", 
//						"publish", "123456Gxg");
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return connection;
	}
	
	public static Connection getMyuallConn() {
		Connection connection = null;
		try {
//				connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
//						"jdbc:mysql://203.171.236.56:3306/myuall?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true", 
//						"root", "qwe!@#");
			
			connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
					"jdbc:mysql://47.94.230.215:13306/myuall?useUnicode=true&characterEncoding=utf8&mysqlEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull", 
					"op", "!wlms*yts_asw");
//				connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
//						"jdbc:mysql://127.0.0.1:3306/myuall?useUnicode=true&characterEncoding=utf8&mysqlEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull", 
//						"root", "");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}

	/**
	 * 获取数据库连接对象，这里只获得，需要自己在使用后进行关闭。
	 *
	 * @return 数据库连接实例
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 * @auther <a href="mailto:g2room@163.com">天涯</a>
	 * Jul 6, 2013 8:49:25 PM
	 */
	public Connection getConn(String driverName, String url, String username, String pwd)
			throws ClassNotFoundException, SQLException {
		// 1.加载驱动
		Class.forName(driverName);
		// 2.得到连接
		return DriverManager.getConnection(url, username, pwd);
	}
	/**
	 * 
	 * 关闭数据库连接。
	 *
	 * @param conn 数据库连接实例
	 * @auther <a href="mailto:g2room@163.com">天涯</a>
	 * Jul 11, 2013 5:06:44 PM
	 */
	public static void closeConn(Connection conn) {
		if(null != conn) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		conn = null;
	}
	
	public static String decodeSpecialCharsWhenLikeUseBackslash(String content) {
        // 单引号是oracle字符串的边界,oralce中用2个单引号代表1个单引号
        String afterDecode = content.replaceAll("'", "''");
        // 由于使用了/作为ESCAPE的转义特殊字符,所以需要对该字符进行转义
        // 这里的作用是将"a/a"转成"a//a"
        afterDecode = afterDecode.replaceAll("/", "//");
        // 使用转义字符 /,对oracle特殊字符% 进行转义,只作为普通查询字符，不是模糊匹配
        afterDecode = afterDecode.replaceAll("%", "/%");
        // 使用转义字符 /,对oracle特殊字符_ 进行转义,只作为普通查询字符，不是模糊匹配
        afterDecode = afterDecode.replaceAll("_", "/_");
        return afterDecode;
    }
	
	/**
     * 获取当前日期
     * @return
     */
	public static String getLastDay() {
		Calendar cal = Calendar.getInstance();
		return date2String(cal.getTime(), "yyyy-MM-dd");
	}
	 
	public static String date2String(Date date, String pattern) {
    	SimpleDateFormat sdf = new SimpleDateFormat(pattern);
    	String dateStr = null;
    	try {
    		dateStr = sdf.format(date);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return dateStr;
    }
}
